package com.exchange.used.config.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

public class ExcelUtil {
    public static void write(File xlsFile, String sheetName, List<String> header, List<List<String>> datas){
        Workbook workbook = null;
        try {
            //在原基础上追加
            workbook = read(xlsFile);

            // 创建工作薄
            //HSSFWorkbook workbook = new HSSFWorkbook();

            // 创建工作表
            Sheet sheet = workbook.createSheet(sheetName);
            Row headerRows = sheet.createRow(0);
            // 添加表头
            for (int col = 0; col < header.size(); col++) {
                headerRows.createCell(col).setCellValue(header.get(col));
            }

            // 添加数据
            for (int row = 0; row < datas.size(); row++) {
                Row dataRows = sheet.createRow(row + 1);
                for (int col = 0; col < datas.get(row).size(); col++) {
                    dataRows.createCell(col).setCellValue(datas.get(row).get(col));
                }
            }

            FileOutputStream xlsStream = new FileOutputStream(xlsFile);
            workbook.write(xlsStream);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (workbook != null){
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static Workbook read(File file){
        Workbook workbook = null;
        try {
            FileInputStream fis = new FileInputStream(file);
            if(file.getName().endsWith(".xls")){  //Excel 2003
                workbook = new HSSFWorkbook(fis);
            }else if(file.getName().endsWith(".xlsx")){  // Excel 2007/2010
                workbook = new XSSFWorkbook(fis);
            }
            return workbook;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public static void removeAllSheet(File xlsFile){
        Workbook workbook = null;
        try {
            workbook = read(xlsFile);
            int sheetNum = workbook.getNumberOfSheets();
            for(int i = 0 ; i < sheetNum ; i ++){
                workbook.removeSheetAt(0);
            }
            FileOutputStream xlsStream = new FileOutputStream(xlsFile);
            workbook.write(xlsStream);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (workbook != null){
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

}
